package com.kaohe.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.kaohe.domain.Category;
import com.kaohe.utils.C3P0Utils;

public class CategoryDao {

	//查询-总条数
	public int count() {
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		String sql = "select count(*) from category";

		Long lo = 0L;
		try {
			lo = (Long) qr.query(sql, new ScalarHandler());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return lo.intValue();
	}

	//查询-当前页内是数据
	public List<Category> cquery(int startIndex, int pageSize) {
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		String sql = "select *from category order by order_number limit ?,?";
		Object[] params = { startIndex, pageSize };
		List<Category> result = null;
		try {
			result = qr.query(sql, params, new BeanListHandler<>(Category.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return result;
	}

	//添加
	public void addcategory(Category category) {
		try {
			QueryRunner qr=new QueryRunner(C3P0Utils.getDataSource());
			String sql="insert into category values(?,?,?,?,?,?)";
			Object[]params={null,category.getCname(),category.getState(),category.getOrder_number(),category.getDescription(),category.getCreate_time()};
			qr.update(sql, params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	//修改-跳转页面
	public Category toUpdateCategory(String cid) {
		
		Category category=null;
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		String sql = "select *from category where cid=?";
		try {
			category = qr.query(sql, cid, new BeanHandler<>(Category.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return category;
		
	}

	//修改-修改数据
	public void update(Category category) {
		try {
			QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
			String sql="update category set cname=?,state=?,order_number=?,description=?,create_time=? where cid=?";
			Object[]params={category.getCname(),category.getState(),category.getOrder_number(),category.getDescription(),category.getCreate_time(),category.getCid()};
			qr.update(sql, params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	
	}

	//删除
	public void decategory(String cid) {
		try {
			QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
			String sql="delete from category where cid=?";
			qr.update(sql, cid);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}
